USE [ACRA_DB_LIVE]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblCompIndex](
	[CompIndex] [int] IDENTITY(1,1) NOT NULL,
	[CaseRefID] [int] NULL,
	[CaseSourceID] [tinyint] NULL,
	[CompCaseRefID] [bigint] NULL,
	[CompCaseSourceID] [tinyint] NULL,
	[CaseRefNo] [varchar](50) NULL,
	[CompCaseRefNo] [varchar](50) NULL,
	[EntityID] [int] NULL,
	[EntityName] [nvarchar](250) NULL,
	[UEN] [varchar](50) NULL,
	[ContactProfileID] [int] NULL,
	[CustomerName] [nvarchar](250) NULL,
	[LangID] [tinyint] NULL,
	[Email] [varchar](250) NULL,
	[ContactNo] [varchar](250) NULL,
	[AgentID] [int] NULL,
	[AgentTeamID] [int] NULL,
	[AgentTenure] [varchar](50) NULL,
	[DateTimeCallEmail] [datetime] NULL,
	[TypeOfCallEmail] [tinyint] NULL,
	[CompType] [varchar](50) NULL,
	[ProcessedBy] [int] NULL,
	[ProcessedByTeamID] [int] NULL,
	[ProcessedOn] [datetime] NULL,
	[SupervisorID] [int] NULL,
	[SupervisorTeamID] [int] NULL,
	[SupervisorStatus] [tinyint] NULL,
	[SupervisedOn] [datetime] NULL,
	[IsValid] [tinyint] NULL,
	[ReviewedBy] [int] NULL,
	[ReviewerStatus] [tinyint] NULL,
	[ReviewedByTeamID] [int] NULL,
	[ReviewedOn] [datetime] NULL,
	[Status] [tinyint] NULL,
	[ModifiedBy] [int] NULL,
	[ModifiedDate] [datetime] NULL,
	[NoOfWorkingDays] [smallint] NULL,
	[NoOfPHAndWeekendDays] [smallint] NULL,
 CONSTRAINT [PK_tblCompIndex] PRIMARY KEY CLUSTERED 
(
	[CompIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[tblComplaint]    Script Date: 10/3/2015 2:32:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblComplaint](
	[IRID] [int] IDENTITY(1,1) NOT NULL,
	[RDate] [datetime] NULL,
	[CaseSummary] [nvarchar](max) NULL,
	[RootCase] [nvarchar](max) NULL,
	[COI1] [tinyint] NULL,
	[COI2] [tinyint] NULL,
	[COI3] [tinyint] NULL,
	[AFB1] [bit] NULL,
	[AFB2] [bit] NULL,
	[AFB3] [bit] NULL,
	[AFB4] [bit] NULL,
	[AFB5] [bit] NULL,
	[AFBSvcAtt] [nvarchar](250) NULL,
	[AFBOthers] [nvarchar](250) NULL,
	[PointNoted] [nvarchar](max) NULL,
	[ATByTLCSM] [nvarchar](max) NULL,
	[RSWCust] [nvarchar](max) NULL,
	[RCA] [nvarchar](max) NULL,
	[KLP] [nvarchar](max) NOT NULL,
	[CPAAndRDOI] [nvarchar](max) NULL,
 CONSTRAINT [PK_tblComplaint] PRIMARY KEY CLUSTERED 
(
	[IRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object:  Table [dbo].[tblCompliment]    Script Date: 10/3/2015 2:32:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblCompliment](
	[CompID] [int] IDENTITY(1,1) NOT NULL,
	[CaseSummary] [nvarchar](max) NULL,
	[RootCase] [nvarchar](max) NULL,
	[COC1] [tinyint] NULL,
	[COC2] [tinyint] NULL,
	[COC3] [tinyint] NULL,
	[ATByTLCSM] [nvarchar](max) NULL,
 CONSTRAINT [PK_tblCompliment] PRIMARY KEY CLUSTERED 
(
	[CompID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblCompIndex] ADD  CONSTRAINT [DF_tblCompIndex_ProcessedOn]  DEFAULT (getdate()) FOR [ProcessedOn]
GO

ALTER TABLE [dbo].[tblCompIndex] ADD  CONSTRAINT [DF_tblCompIndex_Status]  DEFAULT ((0)) FOR [Status]
GO

ALTER TABLE [dbo].[tblCompIndex] ADD  CONSTRAINT [DF_tblCompIndex_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

/****** Object:  StoredProcedure [dbo].[sprocCompCheckCaseRefNo]    Script Date: 10/3/2015 2:34:06 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Phyo
-- Create date: 20 Oct 2013 9:41 AM
-- Description:	Select Task Case Ref No
-- =============================================
--EXEC [sprocTaskCheckTaskCaseRefNo] 'C144-144692', 1, 20678

CREATE PROCEDURE [dbo].[sprocCompCheckCaseRefNo]
	@CaseRefNo varchar(20),
	@CaseSourceID smallint,
	@EntityID int
AS
BEGIN
	
	Declare @CaseRefID bigint
	Declare @CaseProfileIndex bigint
	Declare @CaseStatus tinyint
	Declare @retValue smallint
	Declare @TaskStatus smallint
	Declare @TaskID int

	IF @CaseSourceID = 1
	BEGIN
		Select @CaseRefID = AskACRAID from tblAskACRA Where CaseRefNo = @CaseRefNo 
	END
	ELSE IF @CaseSourceID = 2
	BEGIN
		Select @CaseRefID = EmailID from tblEmail Where EmailCaseRefNo = @CaseRefNo
	END
	ELSE IF @CaseSourceID = 3
	BEGIN
		Select @CaseRefID = VoiceMailID from tblVoiceMail Where VoiceMailCaseRefNo = @CaseRefNo
	END
	ELSE IF @CaseSourceID = 163
	BEGIN
		Select @CaseRefID = CRID from tblCorrespondence Where CRCaseRefNo = @CaseRefNo
	END
	ELSE
	BEGIN
		Select @CaseRefID = ContactHistoryID from tblContactHistory Where CaseRefNo = @CaseRefNo And EntityID = @EntityID And SourceID = @CaseSourceID
	END

	IF @CaseRefID > 0 And @CaseSourceID IN (1, 2, 3, 163)
	BEGIN
		Select @CaseProfileIndex = CaseProfileIndex, @CaseStatus = CaseStatus FROM tblCaseProfileIndex Where EntityID = @EntityID And CaseRefID = @CaseRefID

		If @CaseProfileIndex IS NULL
		BEGIN
			SET @CaseRefID = NULL
		END
	END

	Declare @tmpCaseRefNo varchar(20)

	IF @CaseRefID > 0
	BEGIN
		Select @tmpCaseRefNo = CaseRefNo From tblCompIndex Where CompCaseRefID = @CaseRefID And CompCaseSourceID = @CaseSourceID
	END

	Select @CaseRefID AS CaseRefID, @CaseProfileIndex CaseProfileIndex, @tmpCaseRefNo As CaseRefNo
END


GO

/****** Object:  StoredProcedure [dbo].[sprocCompIndexInsertUpdateSingleItem]    Script Date: 10/3/2015 2:34:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 1:30 PM
-- Description:	Insert/Update CompIndex Details

CREATE PROCEDURE [dbo].[sprocCompIndexInsertUpdateSingleItem]
@CaseRefID int
,@CaseSourceID tinyint
,@UpdateType tinyint = 0
,@UserID int
,@UserStatus tinyint = 0
,@IsValid tinyint = 0
,@Status tinyint = 0
,@CompType varchar(50) = NULL
,@ReviewerTeamID int = NULL
,@CaseRefNo varchar(50) = NULL
,@CompCaseRefNo varchar(50) = NULL
,@CompCaseRefID bigint = NULL
,@CompCaseSourceID tinyint = NULL
,@EntityID int = NULL
,@Entityname nvarchar(250) = NULL
,@UEN varchar(50) = NULL
,@ContactProfileID int = NULL
,@CustomerName nvarchar(250) = NULL
,@ContactNo varchar(250) = NULL
,@Email varchar(250) = NULL
,@LangID tinyint = NULL
,@SupervisorID int = NULL
,@SupervisorTeamID int = NULL
,@SupervisorName nvarchar(250) = NULL
,@AgentID int = NULL
,@AgentTeamID int = NULL
,@AgentName nvarchar(250) = NULL
,@AgentTenure varchar(50) = NULL
,@DateTimeCallEmail datetime = NULL
,@TypeOfCallEmail tinyint = NULL
AS
BEGIN

	Declare @UserTeamID int
	Select @UserTeamID = TeamID from tblUser Where UserID = @UserID

	IF NOT EXISTS(Select CompIndex from tblCompIndex Where CaseRefID = @CaseRefID And CaseSourceID = @CaseSourceID)
	BEGIN
		INSERT INTO tblCompIndex 
		(
			CaseRefID,
			CaseSourceID,
			ProcessedBy,
			ProcessedByTeamID,
			IsValid,
			CaseRefNo,
			CompCaseRefNo,
			CompCaseRefID,
			CompCaseSourceID,
			EntityID,
			EntityName,
			UEN,
			ContactProfileID,
			CustomerName,
			ContactNo,
			Email,
			LangID,
			SupervisorID,
			SupervisorTeamID,
			AgentID,
			AgentTeamID,
			AgentTenure,
			DateTimeCallEmail,
			TypeOfCallEmail,
			CompType,
			ReviewedByTeamID
		)
		Values
		(
			@CaseRefID,
			@CaseSourceID,
			@UserID,
			@UserTeamID,
			@IsValid,
			@CaseRefNo,
			@CompCaseRefNo,
			@CompCaseRefID,
			@CompCaseSourceID,
			@EntityID,
			@EntityName,
			@UEN,
			@ContactProfileID,
			@CustomerName,
			@ContactNo,
			@Email,
			@LangID,
			@SupervisorID,
			@SupervisorTeamID,
			@AgentID,
			@AgentTeamID,
			@AgentTenure,
			@DateTimeCallEmail,
			@TypeOfCallEmail,
			@CompType,
			@ReviewerTeamID
		)
	END
	ELSE IF @UpdateType = 0
	BEGIN
		Update tblCompIndex
		SET IsValid = @IsValid,
		CompCaseRefNo = @CompCaseRefNo,
		CompCaseRefID = @CompCaseRefID,
		CompCaseSourceID = @CompCaseSourceID,
		EntityID = @EntityID,
		Entityname = @EntityName,
		UEN = @UEN,
		ContactProfileID = @ContactProfileID,
		CustomerName = @CustomerName,
		ContactNo = @ContactNo,
		Email = @Email,
		LangID = @LangID,
		SupervisorID = @SupervisorID,
		SupervisorTeamID = @SupervisorTeamID,
		AgentID = @AgentID,
		AgentTeamID = @AgentTeamID,
		AgentTenure = @AgentTenure,
		DateTimeCallEmail = @DateTimeCallEmail,
		TypeOfCallEmail = @TypeOfCallEmail,
		CompType = @CompType,
		ReviewedByTeamID = @ReviewerTeamID,
		[ModifiedBy] = @UserID,
		ModifiedDate = GetDate()
		Where CaseRefID = @CaseRefID And CaseSourceID = @CaseSourceID
	END
	ELSE IF @UpdateType = 1
	BEGIN
		Update tblCompIndex
		SET SupervisorID = @UserID,
			SupervisorTeamID = @UserTeamID,
			SupervisorStatus = @UserStatus,
			SupervisedOn = GetDate(),
			[ModifiedBy] = @UserID,
			ModifiedDate = GetDate()
		Where CaseRefID = @CaseRefID And CaseSourceID = @CaseSourceID
	END
	ELSE IF @UpdateType = 2
	BEGIN
		Update tblCompIndex
		SET ReviewedBy = @UserID,
			ReviewedByTeamID = @UserTeamID,
			ReviewerStatus = @UserStatus,
			ReviewedOn = GetDate(),
			[ModifiedBy] = @UserID,
			ModifiedDate = GetDate()
		Where CaseRefID = @CaseRefID And CaseSourceID = @CaseSourceID
	END
	ELSE IF @UpdateType = 100
	BEGIN
		Update tblCompIndex
		SET 
			SupervisorStatus = @UserStatus,
			[Status] = @Status,
			[ModifiedBy] = @UserID,
			ModifiedDate = GetDate()
		Where CaseRefID = @CaseRefID And CaseSourceID = @CaseSourceID
	END

END
GO

/****** Object:  StoredProcedure [dbo].[sprocCompIndexSelectSingleItem]    Script Date: 10/3/2015 2:34:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 3:42 PM
-- Description:	Select CompIndex Item
-- =============================================
CREATE PROCEDURE [dbo].[sprocCompIndexSelectSingleItem]
	@CaseRefID int,
	@CaseSourceID tinyint
AS
BEGIN
	SELECT CI.[CompIndex]
		  ,CI.[CaseRefID]
		  ,CI.[CaseSourceID]
		  ,CI.[ProcessedBy]
		  ,UP.DisplayName As ProcessedByName
		  ,CI.[ProcessedByTeamID]
		  ,TP.TeamName As ProcessedByTeamName
		  ,CI.[ProcessedOn]
		  ,CI.[SupervisorID]
		  ,US.DisplayName As SupervisorName
		  ,CI.[SupervisorTeamID]
		  ,TS.TeamName As SupervisorTeamName
		  ,CI.[SupervisorStatus]
		  ,CI.[SupervisedOn]
		  ,CI.[IsValid]
		  ,CI.[ReviewedBy]
		  ,UR.DisplayName As ReviewedByName
		  ,CI.[ReviewerStatus]
		  ,CI.[ReviewedByTeamID]
		  ,TR.TeamName As ReviewedByTeamName
		  ,CI.[ReviewedOn]
		  ,CI.[Status]
		  ,CI.[ModifiedDate]
		  ,CI.[NoOfWorkingDays]
		  ,CI.[NoOfPHAndWeekendDays]
	  FROM [tblCompIndex] CI
	  INNER JOIN tblUser UP ON CI.[ProcessedBy] = UP.UserID
	  INNER JOIN tblTeam TP ON CI.[ProcessedByTeamID] = TP.TeamID
	  LEFT OUTER JOIN tblUser US ON CI.[SupervisorID] = US.UserID
	  LEFT OUTER JOIN tblTeam TS ON CI.[SupervisorTeamID] = TS.TeamID
	  LEFT OUTER JOIN tblUser UR ON CI.[ReviewedBy] = UR.UserID
	  LEFT OUTER JOIN tblTeam TR ON CI.[ReviewedByTeamID] = TR.TeamID
	  Where CI.CaseRefID = @CaseRefID And CI.CaseSourceID = @CaseSourceID

END
GO

/****** Object:  StoredProcedure [dbo].[sprocComplaintInsertUpdateSingleItem]    Script Date: 10/3/2015 2:34:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 2:21 PM
-- Description:	Insert/Update Complaint Details
-- =============================================
CREATE PROCEDURE [dbo].[sprocComplaintInsertUpdateSingleItem]
	@IRID int
    ,@IRCaseRefNo varchar(50)
    ,@CaseRefID bigint
    ,@CaseSourceID tinyint
    ,@EntityID int
    ,@EntityName nvarchar(255)
    ,@UEN varchar(50)
    ,@ContactProfileID int
    ,@CustomerName nvarchar(250)
    ,@Email nvarchar(500)
    ,@ContactNo nvarchar(250)
    ,@SupervisorID int
	,@SupervisorTeamID int
    ,@SupervisorName nvarchar(250)
    ,@AgentID int
	,@AgentTeamID int
    ,@AgentName nvarchar(250)
    ,@AgentTenure varchar(50)
    ,@LangID tinyint
    ,@RDate datetime
	,@DateTimeCallEmail datetime
	,@TypeOfCallEmail tinyint
    ,@CaseSummary nvarchar(max)
    ,@RootCase nvarchar(max)
    ,@COI1 tinyint
	,@COI2 tinyint
	,@COI3 tinyint
    ,@AFB1 bit
    ,@AFB2 bit
    ,@AFB3 bit
    ,@AFB4 bit
    ,@AFB5 bit
    ,@AFBSvcAtt nvarchar(250)
    ,@AFBOthers nvarchar(250)
    ,@IsValidFB tinyint
    ,@PointNoted nvarchar(max)
    ,@ATByTLCSM nvarchar(max)
    ,@RSWCust nvarchar(max)
    ,@RCA nvarchar(max)
    ,@KLP nvarchar(max)
    ,@CPAAndRDOI nvarchar(max)
	,@IRType varchar(50)
	,@ReviewerTeamID int
    ,@CreatedBy int
    ,@ModifiedBy int
AS
BEGIN
	Declare @ReturnValue int
	DECLARE
	 @ErrorNumber int
	 ,@UserID int
	 ,@ErrorProcedure varchar(250)	
	 ,@ErrorMessage   varchar(2000)
	 ,@ErrorSeverity  tinyint
	 ,@ErrorState     tinyint
	 ,@ErrorLine	  int

	SET @ReturnValue = -1

	BEGIN TRY
		IF @IRID = -1
			BEGIN
				SET @UserID = @CreatedBy
				IF EXISTS(SELECT 1 FROM tblCompIndex WHERE CompCaseRefId = @CaseRefID And CompCaseSourceID = @CaseSourceID)
				BEGIN
					RAISERROR('Complaint is already exists.', 16, 1)
					RETURN
				END
				ELSE
				BEGIN
					
				INSERT tblComplaint
						   ([RDate]
						   ,[CaseSummary]
						   ,[RootCase]
						   ,[COI1]
						   ,[COI2]
						   ,[COI3]
						   ,[AFB1]
						   ,[AFB2]
						   ,[AFB3]
						   ,[AFB4]
						   ,[AFB5]
						   ,[AFBSvcAtt]
						   ,[AFBOthers]
						   ,[PointNoted]
						   ,[ATByTLCSM]
						   ,[RSWCust]
						   ,[RCA]
						   ,[KLP]
						   ,[CPAAndRDOI])
					 VALUES
						   (@RDate 
						   ,@CaseSummary  
						   ,@RootCase  
						   ,@COI1
						   ,@COI2
						   ,@COI3
						   ,@AFB1
						   ,@AFB2
						   ,@AFB3
						   ,@AFB4
						   ,@AFB5
						   ,@AFBSvcAtt
						   ,@AFBOthers
						   ,@PointNoted
						   ,@ATByTLCSM
						   ,@RSWCust
						   ,@RCA
						   ,@KLP
						   ,@CPAAndRDOI
						   )

				SET @ReturnValue = SCOPE_IDENTITY()
				
				Declare @strCaseRefNo varchar(20)

				Select @strCaseRefNo = 'IR' + RIGHT(CAST(DatePart(YY, GetDate()) as varchar), 2) + CAST(DatePart(month, GetDate()) as varchar) + '-' + RIGHT('0000'+ CONVERT(VARCHAR,@ReturnValue),6)

				EXEC [sprocCompIndexInsertUpdateSingleItem] 
					@CaseRefID = @ReturnValue, 
					@CaseSourceID = 2,
					@UserID = @CreatedBy,
					@IsValid = @IsValidFB,
					@CaseRefNo = @strCaseRefNo,
					@CompCaseRefNo = @IRCaseRefNo,
					@CompCaseRefID = @CaseRefID,
					@CompCaseSourceID = @CaseSourceID,
					@EntityID = @EntityID,
					@UEN = @UEN,
					@EntityName = @EntityName,
					@ContactProfileID = @ContactProfileID,
					@CustomerName = @CustomerName,
					@Email = @Email,
					@ContactNo = @ContactNo,
					@LangID = @LangID,
					@SupervisorID = @SupervisorID,
					@SupervisorTeamID = @SupervisorTeamID,
					@AgentID = @AgentID,
					@AgentTeamID = @AgentTeamID,
					@AgentTenure = @AgentTenure,
					@DateTimeCallEmail = @DateTimeCallEmail,
					@TypeOfCallEmail = @TypeOfCallEmail,
					@CompType = @IRType,
					@ReviewerTeamID = @ReviewerTeamID
			END
		END
		ELSE
		BEGIN
			IF EXISTS(SELECT 1 FROM tblCompIndex WHERE CompCaseRefId = @CaseRefID And CompCaseSourceID = @CaseSourceID And CaseRefID <> @IRID And CaseSourceID = 2)
			BEGIN
				RAISERROR('Complaint is already exists.', 16, 1)
				RETURN
			END
			ELSE
			BEGIN
				SET @UserID = @ModifiedBy
				
				EXEC [sprocCompIndexInsertUpdateSingleItem] 
					@CaseRefID = @IRID, 
					@CaseSourceID = 2,
					@UserID = @ModifiedBy,
					@IsValid = @IsValidFB,
					@CaseRefNo = @strCaseRefNo,
					@CompCaseRefNo = @IRCaseRefNo,
					@CompCaseRefID = @CaseRefID,
					@CompCaseSourceID = @CaseSourceID,
					@EntityID = @EntityID,
					@UEN = @UEN,
					@EntityName = @EntityName,
					@ContactProfileID = @ContactProfileID,
					@CustomerName = @CustomerName,
					@Email = @Email,
					@ContactNo = @ContactNo,
					@LangID = @LangID,
					@SupervisorID = @SupervisorID,
					@SupervisorTeamID = @SupervisorTeamID,
					@AgentID = @AgentID,
					@AgentTeamID = @AgentTeamID,
					@AgentTenure = @AgentTenure,
					@DateTimeCallEmail = @DateTimeCallEmail,
					@TypeOfCallEmail = @TypeOfCallEmail,
					@CompType = @IRType,
					@ReviewerTeamID = @ReviewerTeamID

				UPDATE [dbo].[tblComplaint]
				   SET 
					  [RDate] = @RDate 
					  ,[CaseSummary] = @CaseSummary 
					  ,[RootCase] = @RootCase 
					  ,[COI1] = @COI1
					  ,[COI2] = @COI2
					  ,[COI3] = @COI3
					  ,[AFB1] = @AFB1 
					  ,[AFB2] = @AFB2 
					  ,[AFB3] = @AFB3 
					  ,[AFB4] = @AFB4 
					  ,[AFB5] = @AFB5 
					  ,[AFBSvcAtt] = @AFBSvcAtt 
					  ,[AFBOthers] = @AFBOthers 
					  ,[PointNoted] = @PointNoted 
					  ,[ATByTLCSM] = @ATByTLCSM 
					  ,[RSWCust] = @RSWCust 
					  ,[RCA] = @RCA 
					  ,[KLP] = @KLP 
					  ,[CPAAndRDOI] = @CPAAndRDOI 
				WHERE IRID = @IRID
				SET @ReturnValue = @IRID
			END
		END
	END TRY
	BEGIN CATCH
		SET @ErrorNumber = ERROR_NUMBER()
		SET @ErrorProcedure = ERROR_PROCEDURE()
		SET @ErrorMessage  = ERROR_MESSAGE()
		SET @ErrorSeverity = ERROR_SEVERITY()
		SET @ErrorState    = ERROR_STATE()
		SET @ErrorLine = ERROR_LINE()

		EXEC sprocErrorLogInsertUpdateSingleItem @ErrorNumber, @UserID, @ErrorProcedure, @ErrorMessage, @ErrorLine, @ErrorSeverity, @ErrorState

		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	END CATCH

	Return @ReturnValue
END

GO

/****** Object:  StoredProcedure [dbo].[sprocComplaintSelectSingleItem]    Script Date: 10/3/2015 2:34:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 3:42 PM
-- Description:	Select CompIndex Item
-- =============================================
CREATE PROCEDURE [dbo].[sprocComplaintSelectSingleItem]
	@CaseRefID bigint,
	@CaseSourceID tinyint
AS
BEGIN
	SELECT [IRID]
      ,CI.[CaseRefNo]
      ,CI.CompCaseRefNo As [IRCaseRefNo]
      ,CI.[CompCaseRefID] As CaseRefID
	  ,CI.[CompCaseSourceID] As CaseSourceID
      ,CI.[EntityID]
      ,CI.[EntityName]
      ,CI.[UEN]
      ,CI.[ContactProfileID]
      ,CI.CustomerName
      ,CI.[Email]
      ,CI.[ContactNo]
      ,CI.[SupervisorID]
	  ,U2.DisplayName As [SupervisorName]
	  ,CI.[SupervisorTeamID]
      ,T2.TeamName As [SupervisorTeamName]
      ,CI.[AgentID]
	  ,U.DisplayName As AgentName
	  ,CI.[AgentTeamID]
	  ,T.TeamName As AgentTeamName
      ,[AgentTenure]
	  ,CI.LangID
      ,L.Description As [Language]
	  ,CI.[DateTimeCallEmail]
      ,CI.[TypeOfCallEmail]
	  ,CI.CompType As IRType
	  ,CI.ReviewedByTeamID As [ReviewerTeamID]
	  ,T3.TeamName As [ReviewerTeamName]
	  ,U3.DisplayName As ReviewerName
      ,C.[RDate]
      ,C.[CaseSummary]
      ,C.[RootCase]
      ,C.[COI1]
	  ,C.[COI2]
	  ,C.[COI3]
      ,C.[AFB1]
      ,C.[AFB2]
      ,C.[AFB3]
      ,C.[AFB4]
      ,C.[AFB5]
      ,C.[AFBSvcAtt]
      ,C.[AFBOthers]
      ,CI.IsValid As [IsValidFB]
      ,C.[PointNoted]
      ,C.[ATByTLCSM]
      ,C.[RSWCust]
      ,C.[RCA]
      ,C.[KLP]
      ,C.[CPAAndRDOI]
      ,CI.ProcessedOn As [CreatedDate]
      ,CI.ProcessedBy As [CreatedBy]
	  ,U1.DisplayName As [CreatedByName]
      ,CI.[ModifiedDate]
      ,CI.[ModifiedBy]
	FROM [dbo].[tblComplaint] C
	INNER JOIN tblCompIndex CI ON C.IRID = CI.CaseRefId And CI.CaseSourceID = 2
	INNER JOIN tblLanguageInfo L ON CI.LangID = L.LangID
	INNER JOIN tblUser U ON CI.AgentID = U.UserID
	INNER JOIN tblTeam T ON CI.AgentTeamID = T.TeamID
	INNER JOIN tblUser U1 ON CI.ProcessedBy = U1.UserID
	INNER JOIN tblTeam T1 ON CI.ProcessedByTeamID = T1.TeamID
	INNER JOIN tblUser U2 ON CI.SupervisorID = U2.UserID
	INNER JOIN tblTeam T2 ON CI.SupervisorTeamID = T2.TeamID
	LEFT OUTER JOIN tblUser U3 ON CI.ReviewedBy = U3.UserID
	LEFT OUTER JOIN tblTeam T3 ON CI.ReviewedByTeamID = T3.TeamID
	Where CI.CompCaseRefID = @CaseRefID And CI.CompCaseSourceID = @CaseSourceID
END


GO

/****** Object:  StoredProcedure [dbo].[sprocComplimentInsertUpdateSingleItem]    Script Date: 10/3/2015 2:34:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 1:41 PM
-- Description:	Insert/Update Compliment Details
-- =============================================
CREATE PROCEDURE [dbo].[sprocComplimentInsertUpdateSingleItem]
	@CompID int
    ,@CompCaseRefNo varchar(50)
    ,@CaseRefID bigint
    ,@CaseSourceID tinyint
    ,@EntityID int
    ,@EntityName nvarchar(255)
    ,@UEN varchar(50)
    ,@ContactProfileID int
    ,@CustomerName nvarchar(250)
    ,@Email nvarchar(500)
    ,@ContactNo nvarchar(250)
    ,@SupervisorID int
	,@SupervisorTeamID int
    ,@SupervisorName nvarchar(250)
    ,@AgentID int
	,@AgentTeamID int
    ,@AgentName nvarchar(250)
    ,@AgentTenure varchar(50)
    ,@LangID tinyint
    ,@DateTimeCallEmail datetime
	,@TypeOfCallEmail tinyint
    ,@CaseSummary nvarchar(max)
    ,@RootCase nvarchar(max)
    ,@COC1 tinyint
	,@COC2 tinyint
	,@COC3 tinyint
    ,@IsValidComp tinyint
    ,@CompType varchar(50)
    ,@ATByTLCSM nvarchar(max)
	,@ReviewerTeamID int
    ,@CreatedBy int
    ,@ModifiedBy int
AS
BEGIN
	Declare @ReturnValue int
	DECLARE
	 @ErrorNumber int
	 ,@UserID int
	 ,@ErrorProcedure varchar(250)	
	 ,@ErrorMessage   varchar(2000)
	 ,@ErrorSeverity  tinyint
	 ,@ErrorState     tinyint
	 ,@ErrorLine	  int

	SET @ReturnValue = -1

	BEGIN TRY
		IF @CompID = -1
			BEGIN
				SET @UserID = @CreatedBy
				IF EXISTS(SELECT 1 FROM tblCompIndex WHERE CompCaseRefId = @CaseRefID And CompCaseSourceID = @CaseSourceID)
				BEGIN
					RAISERROR('Compliment is already exists.', 16, 1)
					RETURN
				END
				ELSE
				BEGIN
					
				INSERT tblCompliment
						   ([CaseSummary]
						   ,[RootCase]
						   ,[COC1]
						   ,[COC2]
						   ,[COC3]
						   ,[ATByTLCSM]
						   )
					 VALUES
						   (@CaseSummary  
						   ,@RootCase  
						   ,@COC1
						   ,@COC2
						   ,@COC3  
						   ,@ATByTLCSM
						   )

				SET @ReturnValue = SCOPE_IDENTITY()
				
				Declare @strCaseRefNo varchar(20)

				Select @strCaseRefNo = 'CP' + RIGHT(CAST(DatePart(YY, GetDate()) as varchar), 2) + CAST(DatePart(month, GetDate()) as varchar) + '-' + RIGHT('0000'+ CONVERT(VARCHAR,@ReturnValue),6)

				EXEC [sprocCompIndexInsertUpdateSingleItem] 
					@CaseRefID = @ReturnValue, 
					@CaseSourceID = 1,
					@UserID = @CreatedBy,
					@IsValid = @IsValidComp,
					@CaseRefNo = @strCaseRefNo,
					@CompCaseRefNo = @CompCaseRefNo,
					@CompCaseRefID = @CaseRefID,
					@CompCaseSourceID = @CaseSourceID,
					@EntityID = @EntityID,
					@UEN = @UEN,
					@EntityName = @EntityName,
					@ContactProfileID = @ContactProfileID,
					@CustomerName = @CustomerName,
					@Email = @Email,
					@ContactNo = @ContactNo,
					@LangID = @LangID,
					@SupervisorID = @SupervisorID,
					@SupervisorTeamID = @SupervisorTeamID,
					@AgentID = @AgentID,
					@AgentTeamID = @AgentTeamID,
					@AgentTenure = @AgentTenure,
					@DateTimeCallEmail = @DateTimeCallEmail,
					@TypeOfCallEmail = @TypeOfCallEmail,
					@CompType = @CompType,
					@ReviewerTeamID = @ReviewerTeamID
			END
		END
		ELSE
		BEGIN
			IF EXISTS(SELECT 1 FROM tblCompIndex WHERE CompCaseRefId = @CaseRefID And CompCaseSourceID = @CaseSourceID And CaseRefID <> @CompID And CaseSourceID = 1)
			BEGIN
				RAISERROR('Compliment is already exists.', 16, 1)
				RETURN
			END
			ELSE
			BEGIN
				SET @UserID = @ModifiedBy
				
				EXEC [sprocCompIndexInsertUpdateSingleItem] 
					@CaseRefID = @CompID, 
					@CaseSourceID = 1,
					@UserID = @ModifiedBy,
					@IsValid = @IsValidComp,
					@CaseRefNo = @strCaseRefNo,
					@CompCaseRefNo = @CompCaseRefNo,
					@CompCaseRefID = @CaseRefID,
					@CompCaseSourceID = @CaseSourceID,
					@EntityID = @EntityID,
					@UEN = @UEN,
					@EntityName = @EntityName,
					@ContactProfileID = @ContactProfileID,
					@CustomerName = @CustomerName,
					@Email = @Email,
					@ContactNo = @ContactNo,
					@LangID = @LangID,
					@SupervisorID = @SupervisorID,
					@SupervisorTeamID = @SupervisorTeamID,
					@AgentID = @AgentID,
					@AgentTeamID = @AgentTeamID,
					@AgentTenure = @AgentTenure,
					@DateTimeCallEmail = @DateTimeCallEmail,
					@TypeOfCallEmail = @TypeOfCallEmail,
					@CompType = @CompType,
					@ReviewerTeamID = @ReviewerTeamID

				UPDATE [dbo].[tblCompliment]
				   SET [CaseSummary] = @CaseSummary 
					  ,[RootCase] = @RootCase 
					  ,[COC1] = @COC1
					  ,[COC2] = @COC2
					  ,[COC3] = @COC3
					  ,[ATByTLCSM] = @ATByTLCSM 
				WHERE CompID = @CompID

				SET @ReturnValue = @CompID
			END
		END
	END TRY
	BEGIN CATCH
		SET @ErrorNumber = ERROR_NUMBER()
		SET @ErrorProcedure = ERROR_PROCEDURE()
		SET @ErrorMessage  = ERROR_MESSAGE()
		SET @ErrorSeverity = ERROR_SEVERITY()
		SET @ErrorState    = ERROR_STATE()
		SET @ErrorLine = ERROR_LINE()

		EXEC sprocErrorLogInsertUpdateSingleItem @ErrorNumber, @UserID, @ErrorProcedure, @ErrorMessage, @ErrorLine, @ErrorSeverity, @ErrorState

		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
	END CATCH

	Return @ReturnValue
END




GO

/****** Object:  StoredProcedure [dbo].[sprocComplimentSelectSingleItem]    Script Date: 10/3/2015 2:34:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Phyo
-- Create date: 5 Dec 2014 3:42 PM
-- Description:	Select CompIndex Item
-- =============================================
CREATE PROCEDURE [dbo].[sprocComplimentSelectSingleItem]
	@CaseRefID bigint,
	@CaseSourceID tinyint
AS
BEGIN

	SELECT C.[CompID]
		  ,CI.[CaseRefNo]
		  ,CI.CompCaseRefNo
		  ,CI.[CompCaseRefID] As CaseRefID
		  ,CI.[CompCaseSourceID] As CaseSourceID
		  ,CI.[EntityID]
		  ,CI.[EntityName]
		  ,CI.[UEN]
		  ,CI.[ContactProfileID]
		  ,CI.CustomerName
		  ,CI.[Email]
		  ,CI.[ContactNo]
		  ,CI.[SupervisorID]
		  ,U2.DisplayName As [SupervisorName]
		  ,CI.[SupervisorTeamID]
		  ,T2.TeamName As [SupervisorTeamName]
		  ,CI.[AgentID]
		  ,U.DisplayName As AgentName
		  ,CI.[AgentTeamID]
		  ,T.TeamName As AgentTeamName
		  ,[AgentTenure]
		  ,CI.LangID
		  ,L.Description As [Language]
		  ,CI.[DateTimeCallEmail]
		  ,CI.[TypeOfCallEmail]
		  ,CI.CompType As IRType
		  ,CI.ReviewedByTeamID As [ReviewerTeamID]
		  ,T3.TeamName As [ReviewerTeamName]
		  ,U3.DisplayName As ReviewerName
		  ,[CaseSummary]
		  ,[RootCase]
		  ,[COC1]
		  ,[COC2]
		  ,[COC3]
		  ,CI.[IsValid] As [IsValidComp]
		  ,CI.[CompType]
		  ,C.[ATByTLCSM]
		  ,CI.ProcessedOn As [CreatedDate]
		  ,CI.ProcessedBy As [CreatedBy]
		  ,U1.DisplayName As [CreatedByName]
		  ,CI.[ModifiedDate]
		  ,CI.[ModifiedBy]
	  FROM [dbo].[tblCompliment] C
		INNER JOIN tblCompIndex CI ON C.CompID = CI.CaseRefId And CI.CaseSourceID = 1
		INNER JOIN tblLanguageInfo L ON CI.LangID = L.LangID
		INNER JOIN tblUser U ON CI.AgentID = U.UserID
		INNER JOIN tblTeam T ON CI.AgentTeamID = T.TeamID
		INNER JOIN tblUser U1 ON CI.ProcessedBy = U1.UserID
		INNER JOIN tblTeam T1 ON CI.ProcessedByTeamID = T1.TeamID
		INNER JOIN tblUser U2 ON CI.SupervisorID = U2.UserID
		INNER JOIN tblTeam T2 ON CI.SupervisorTeamID = T2.TeamID
		LEFT OUTER JOIN tblUser U3 ON CI.ReviewedBy = U3.UserID
		LEFT OUTER JOIN tblTeam T3 ON CI.ReviewedByTeamID = T3.TeamID
	  Where CI.CompCaseRefID = @CaseRefID And CI.CompCaseSourceID = @CaseSourceID

END
GO

/****** Object:  StoredProcedure [dbo].[sprocCompSelectItemList]    Script Date: 10/3/2015 2:34:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Phyo
-- Create date: 9 Dec 2014 5:50 PM
-- Description:	Select Complaint & Compliment List
-- =============================================
CREATE PROCEDURE [dbo].[sprocCompSelectItemList]
	@CaseID varchar(20) = NULL,
	@CaseSourceID smallint = NULL,
	@CreatedDate date = NULL,
	@EndDate date = NULL,
	@Status smallint = NULL,
	@TeamID int = NULL,
	@UserID int = NULL,
	@ReportType tinyint = 0
AS
BEGIN
	SELECT CI.CompIndex
		,CI.[CaseRefNo]
		,CI.CompCaseRefNo
		,CI.[CaseRefID]
		,CI.[CaseSourceID]
		,CI.IsValid
		,CASE CI.IsValid
			WHEN 1 THEN 'Yes'
			WHEN 2 THEN 'No'
			ELSE 'NA' END IsValidDesc
		,CASE CI.[CaseSourceID]
			WHEN 1 THEN 'Compliment'
			WHEN 2 THEN 'Complaint'
		END CaseSourceDesc
		,CI.CompCaseRefID
		,CI.CompCaseSourceID
		,S.Description As CompCaseSourceDesc
		,CI.[EntityID]
		,CI.[EntityName]
		,CI.[UEN]
		,CI.[ContactProfileID]
		,CI.CustomerName
		,CI.[Email]
		,CI.[ContactNo]
		,CI.SupervisedOn
		,CI.[SupervisorID]
		,U2.DisplayName As [SupervisorName]
		,CI.[SupervisorTeamID]
		,T2.TeamName As [SupervisorTeamName]
		,CASE CI.[SupervisorStatus]
			WHEN 0 THEN '-'
			WHEN 1 THEN 'OK'
			WHEN 100 THEN 'COMPLETE'
			ELSE ''
		 END [SupervisorStatus]

		,CI.[AgentID]
		,U.DisplayName As AgentName
		,CI.[AgentTeamID]
		,T.TeamName As AgentTeamName
		,[AgentTenure]
		,CI.LangID
		,L.Description As [Language]
		,CI.[DateTimeCallEmail]
		,CI.[TypeOfCallEmail]
		,CI.CompType As IRType
		,CI.ReviewedByTeamID As [ReviewerTeamID]
		,T3.TeamName As [ReviewerTeamName]
		,U3.DisplayName As ReviewerName
		,CI.ReviewedOn
		,CASE CI.[ReviewerStatus]
			WHEN 0 THEN '-'
			WHEN 1 THEN 'OK'
			WHEN 2 THEN 'NOT OK'
			ELSE ''
		 END [ReviewerStatus]
		,ProcessedOn
		,CI.ProcessedBy
		,U1.DisplayName ProcessedByName
		,CI.[ModifiedDate]
		,CI.[ModifiedBy]
	FROM tblCompIndex CI 
	INNER JOIN tblLanguageInfo L ON CI.LangID = L.LangID
	INNER JOIN tblSourceInfo S ON CI.CompCaseSourceID = S.SourceID
	INNER JOIN tblUser U ON CI.AgentID = U.UserID
	INNER JOIN tblTeam T ON CI.AgentTeamID = T.TeamID
	INNER JOIN tblUser U1 ON CI.ProcessedBy = U1.UserID
	INNER JOIN tblTeam T1 ON CI.ProcessedByTeamID = T1.TeamID
	INNER JOIN tblUser U2 ON CI.SupervisorID = U2.UserID
	INNER JOIN tblTeam T2 ON CI.SupervisorTeamID = T2.TeamID
	LEFT OUTER JOIN tblUser U3 ON CI.ReviewedBy = U3.UserID
	LEFT OUTER JOIN tblTeam T3 ON CI.ReviewedByTeamID = T3.TeamID
	WHERE
	(@CaseID IS NULL OR (CI.[CaseRefNo] IS NOT NULL And (RIGHT(CI.[CaseRefNo],LEN(@CaseID)) = @CaseID) OR RIGHT(CI.[CompCaseRefNo],LEN(@CaseID)) = @CaseID))
	AND (@CaseSourceID IS NULL OR (CI.[CaseSourceID] IS NOT NULL And CI.[CaseSourceID] = @CaseSourceID))
	AND (@Status IS NULL OR (CI.[Status] IS NOT NULL And CI.[Status] = @Status))
	AND (@TeamID IS NULL OR ((CI.[ProcessedByTeamID] = @TeamID) OR (CI.[SupervisorTeamID] = @TeamID) OR (CI.[ReviewedByTeamID] = @TeamID)))
	AND ((@ReportType = 0 AND (@CreatedDate IS NULL OR (CI.ProcessedOn IS NOT NULL And Convert(varchar(8), CI.ProcessedOn, 112) = Convert(varchar(8), @CreatedDate, 112))))
			OR (@ReportType = 1 AND (@CreatedDate IS NULL OR (CI.ProcessedOn IS NOT NULL And Convert(varchar(6), CI.ProcessedOn, 112) = Convert(varchar(6), @CreatedDate, 112))))
			OR (@ReportType = 2 AND (@CreatedDate IS NULL OR (CI.ProcessedOn IS NOT NULL And CI.ProcessedOn Between @CreatedDate And @EndDate))))
END


GO

/****** Object:  StoredProcedure [dbo].[sprocCompSelectItemListByEntity]    Script Date: 10/3/2015 2:34:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Phyo
-- Create date: 09 Dec 2014 2:27 PM
-- Description:	Select Complaint & Compliment History List
-- =============================================
CREATE PROCEDURE [dbo].[sprocCompSelectItemListByEntity]
 @EntityID int,
 @IsShowAllStatus bit = 0,
 @ContactProfileID int = NULL
AS
BEGIN

	Declare @tblTmp table
	(
		CompIndex int
	)

	IF @IsShowAllStatus = 0
	BEGIN
		INSERT INTO @tblTmp
		Select Top 10 CI.CompIndex
		FROM  tblCompIndex CI
		WHERE CI.EntityID = @EntityID
		AND (@ContactProfileID IS NULL OR (@ContactProfileID IS NOT NULL AND CI.ContactProfileID = @ContactProfileID))
		And (CI.SupervisorStatus IS NULL OR (CI.SupervisorStatus <> 100))
		Order By CI.CompIndex DESC
	END
	ELSE IF @IsShowAllStatus = 1
	BEGIN
		INSERT INTO @tblTmp
		Select Top 10 CI.CompIndex
		FROM  tblCompIndex CI
		WHERE CI.EntityID = @EntityID
		AND (@ContactProfileID IS NULL OR (@ContactProfileID IS NOT NULL AND CI.ContactProfileID = @ContactProfileID))
		Order By CI.CompIndex DESC
	END

	SELECT CI.CompIndex
		,CI.[CaseRefNo]
		,CI.CompCaseRefNo
		,CI.[CaseRefID]
		,CI.[CaseSourceID]
		,CI.IsValid
		,CASE CI.IsValid
			WHEN 1 THEN 'Yes'
			WHEN 2 THEN 'No'
			ELSE 'NA' END IsValidDesc
		,CASE CI.[CaseSourceID]
			WHEN 1 THEN 'Compliment'
			WHEN 2 THEN 'Complaint'
		END CaseSourceDesc
		,CI.CompCaseRefID
		,CI.CompCaseSourceID
		,S.Description As CompCaseSourceDesc
		,CI.[EntityID]
		,CI.[EntityName]
		,CI.[UEN]
		,CI.[ContactProfileID]
		,CI.CustomerName
		,CI.[Email]
		,CI.[ContactNo]
		,CI.SupervisedOn
		,CI.[SupervisorID]
		,U2.DisplayName As [SupervisorName]
		,CI.[SupervisorTeamID]
		,T2.TeamName As [SupervisorTeamName]
		,CASE CI.[SupervisorStatus]
			WHEN 0 THEN '-'
			WHEN 1 THEN 'OK'
			WHEN 100 THEN 'COMPLETE'
			ELSE ''
		 END [SupervisorStatus]

		,CI.[AgentID]
		,U.DisplayName As AgentName
		,CI.[AgentTeamID]
		,T.TeamName As AgentTeamName
		,[AgentTenure]
		,CI.LangID
		,L.Description As [Language]
		,CI.[DateTimeCallEmail]
		,CI.[TypeOfCallEmail]
		,CI.CompType As IRType
		,CI.ReviewedByTeamID As [ReviewerTeamID]
		,T3.TeamName As [ReviewerTeamName]
		,U3.DisplayName As ReviewerName
		,CI.ReviewedOn
		,CASE CI.[ReviewerStatus]
			WHEN 0 THEN '-'
			WHEN 1 THEN 'OK'
			WHEN 2 THEN 'NOT OK'
			ELSE ''
		 END [ReviewerStatus]
		,ProcessedOn
		,CI.ProcessedBy
		,U1.DisplayName ProcessedByName
		,CI.[ModifiedDate]
		,CI.[ModifiedBy]
	FROM tblCompIndex CI 
	INNER JOIN @tblTmp TMP ON CI.CompIndex = TMP.CompIndex
	INNER JOIN tblLanguageInfo L ON CI.LangID = L.LangID
	INNER JOIN tblSourceInfo S ON CI.CompCaseSourceID = S.SourceID
	INNER JOIN tblUser U ON CI.AgentID = U.UserID
	INNER JOIN tblTeam T ON CI.AgentTeamID = T.TeamID
	INNER JOIN tblUser U1 ON CI.ProcessedBy = U1.UserID
	INNER JOIN tblTeam T1 ON CI.ProcessedByTeamID = T1.TeamID
	INNER JOIN tblUser U2 ON CI.SupervisorID = U2.UserID
	INNER JOIN tblTeam T2 ON CI.SupervisorTeamID = T2.TeamID
	LEFT OUTER JOIN tblUser U3 ON CI.ReviewedBy = U3.UserID
	LEFT OUTER JOIN tblTeam T3 ON CI.ReviewedByTeamID = T3.TeamID
	Order By CI.CompIndex DESC
END

GO

/****** Object:  StoredProcedure [dbo].[sprocContactHistoryComplimentInsertUpdateSingleItem]    Script Date: 10/3/2015 2:37:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sprocContactHistoryComplimentInsertUpdateSingleItem]
@ContactHistoryID bigint,
@IsValid tinyint = 0,
@CheckedBy int = 0
AS

IF @ContactHistoryID > 0
BEGIN
	IF NOT EXISTS(Select CompID from tblContactHistoryCompliment Where ContactHistoryID = @ContactHistoryID)
	BEGIN
		INSERT INTO tblContactHistoryCompliment 
		(
			ContactHistoryID
		)
		Values
		(
			@ContactHistoryID
		)
	END

	IF @CheckedBy > 0 
	BEGIN
		Update tblContactHistoryCompliment
		SET IsValid = @IsValid,
			CheckedBy = @CheckedBy,
			CheckedDate = GETDATE()
		WHERE ContactHistoryID = @ContactHistoryID
	END
END
GO

/****** Object:  StoredProcedure [dbo].[sprocContactHistoryComplimentSelectItemList]    Script Date: 10/3/2015 2:55:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Phyo
-- Create date: 19 Oct 2013 3:11 PM
-- Description:	Select Contact History List
-- =============================================
--Declare @ReportDate date
--SET @ReportDate = GetDate()

--EXEC [sprocContactHistorySelectItemList]
--	@TeamID = NULL,
--	@LogBy = NULL,
--    @CreatedDate = @ReportDate,
--	@IsCallBack = NULL,
--	@CallBackDate = NULL,
--	@ReportType = 0,
--	@NoReview = 1

CREATE PROCEDURE [dbo].[sprocContactHistoryComplimentSelectItemList]
	@TeamID int = NULL,
	@LogBy int = NULL,
	@CreatedDate date = NULL,
	@CheckStatus tinyint = 0,
	@ReportType tinyint = 0
AS
BEGIN
	Select CH.ContactHistoryID,
			CH.CaseRefNo,
			CH.EntityID,
			CH.ContactProfileID,
			CP.Salutation + ' ' + CP.SurName + ' ' + CP.Name As CustName,
			CH.SourceID,
			CASE 
				WHEN IsComplaint = 1 THEN 'Complaint'
				WHEN IsCompliment = 1 THEN 'Compliment'
				WHEN IsSuggesstion = 1 THEN 'Suggesstion'
				WHEN IsFeedback = 1 THEN 'Feedback'
				WHEN IsEnquiry = 1 THEN 'Enquiry'
			END TypeOfEnquiry,
			S.[Description] As SourceDesc, 
			CH.EnquiryTypeID,
			EN.[Description] AS EnquiryTypeDesc,
			CH.EntityID,
			E.EntityName,
			ET.EntityTypeID,
			ET.[Description] AS EntityTypeDesc,
			TT.TranTypeID,
			TT.[Description] AS TranTypeDes,
			CH.PhoneID,
			CH.LogStart,
			CH.LogEnd,
			CH.LogBy,
			CH.IsCallBack,
			CH.CallBackDateTime,
			LS.LogSummary,
			T.TeamName,
			U.DisplayName,
			CH.CaseTypeID,
			CT.[Description] AS CaseTypeDesc,
			CH.CreatedDate,
			CH.IsRepeatCall,
			CC.IsValid,
			CC.CheckedBy,
			U2.DisplayName As CheckByUserName,
			CC.CheckedDate
	FROM tblContactHistory CH 
	INNER JOIN tblContactHistoryLogSummary LS ON CH.ContactHistoryID = LS.ContactHistoryID
	INNER JOIN tblContactHistoryCompliment CC ON CH.ContactHistoryID = CC.ContactHistoryID
	INNER JOIN tblEntity E ON CH.EntityID = E.EntityID
	INNER JOIN tblContactProfile CP ON CH.ContactProfileID = CP.ContactProfileID
	INNER JOIN tblSourceInfo S ON CH.SourceID = S.SourceID
	INNER JOIN tblCaseTypeInfo CT ON CH.CaseTypeID = CT.CaseTypeID
	INNER JOIN tblEnquiryTypeInfo EN ON CH.EnquiryTypeID = EN.EnquiryTypeID
	INNER JOIN tblTranTypeInfo TT ON EN.TranTypeID = TT.TranTypeID
	INNER JOIN tblEntityTypeInfo ET ON ET.EntityTypeID = TT.EntityTypeID
	INNER JOIN tblUser U ON CH.LogBy = U.UserID
	INNER JOIN tblTeam T ON U.TeamID = T.TeamID
	LEFT OUTER JOIN tblUser U2 ON CC.CheckedBy = U2.UserID

	WHERE 
	(@CheckStatus = 100 OR ((@CheckStatus = 0 And CC.CheckedBy IS NULL) OR (@CheckStatus = 1 And CC.CheckedBy IS NOT NULL)))
	And (@LogBy IS NULL OR (CH.LogBy IS NOT NULL AND CH.LogBy = @LogBy))
	AND (@TeamID IS NULL OR (U.TeamID IS NOT NULL AND U.TeamID = @TeamID))
	AND ((@ReportType = 0 AND (@CreatedDate IS NULL OR (CH.CreatedDate IS NOT NULL And Convert(varchar(8), CH.CreatedDate, 112) = Convert(varchar(8), @CreatedDate, 112))))
			OR (@ReportType = 1 AND (@CreatedDate IS NULL OR (CH.CreatedDate IS NOT NULL And Convert(varchar(6), CH.CreatedDate, 112) = Convert(varchar(6), @CreatedDate, 112)))))
	Order By CH.CreatedDate ASC
END

GO

/****** Object:  StoredProcedure [dbo].[sprocCheckCaseRefNo]    Script Date: 10/3/2015 3:03:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC sprocCheckCaseRefNo 0, 0, 0
CREATE PROCEDURE [dbo].[sprocCheckCaseRefNo]
(
	@CaseRefID bigint,
	@CaseSourceID tinyint
)
As
BEGIN
	Declare @CaseRefNo varchar(20)

	IF @CaseSourceID = 1
	BEGIN
		Select @CaseRefNo = CaseRefNo FROM tblAskACRA Where AskACRAID = @CaseRefID
	END
	ELSE IF @CaseSourceID = 2
	BEGIN
		Select @CaseRefNo = EmailCaseRefNo from tblEmail Where EmailID = @CaseRefID
	END
	ELSE IF @CaseSourceID = 3
	BEGIN
		Select @CaseRefNo = VoiceMailCaseRefNo from tblVoiceMail Where VoiceMailID = @CaseRefID
	END
	ELSE IF @CaseSourceID = 163
	BEGIN
		Select @CaseRefNo = CRCaseRefNo from tblCorrespondence Where @CaseRefID = CRID
	END
	ELSE
	BEGIN
		Select @CaseRefNo = CaseRefNo from tblContactHistory Where ContactHistoryID = @CaseRefID
	END

	IF @CaseRefNo IS NULL SET @CaseRefNo = ''
	
	Select @CaseRefNo CaseRefNo
END